<?php
class Customer extends DB
{
    var $_table = 'customer';
    function __construct()
    {
	parent::__construct();
    }

    function auth($login,$password)
    {
	$this->_query = sprintf('SELECT * FROM `customer` WHERE `email`=\'%s\' AND `password`=\'%s\' LIMIT 1',mysql_escape_string($login),mysql_escape_string($password));

	if ( $this->_result = mysql_query($this->_query) )
	{
	    return mysql_fetch_assoc($this->_result);
	}
    }

    function accountById($customer_id)
    {
	if ( is_numeric($customer_id) )
	{
	    $this->_query = sprintf('SELECT `account_id` FROM `customer` WHERE `id`=%d',$customer_id);

	    if ( $result = $this->_doQuery($this->_query) )
	    {
		$r = mysql_fetch_row($result);

		return $r[0];
	    }
	}
    }

    function getList()
    {
	$this->_query = sprintf
	(
	    'SELECT `%1$s`.*,'
	    .'(SELECT COUNT(*) FROM `invoice` i JOIN `contract` c ON `c`.`id`=`i`.`contract_id` WHERE `c`.`account_id`=`%1$s`.`account_id` AND `i`.`date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\') AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))) AS `documents_total`,'
	    .'(SELECT COUNT(*) FROM `invoice` i JOIN `contract` c ON `c`.`id`=`i`.`contract_id` WHERE `c`.`account_id`=`%1$s`.`account_id` AND `i`.`date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\') AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND `printed`=1) AS `documents_printed`,'
/*
	    .'(SELECT COUNT(*) FROM `document` WHERE `type`=\'ttk\' AND `document`.`account_id`=`%1$s`.`account_id` AND `document_date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\') AND CONCAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)),\' 23:59:59\')) AS `documents_total`,'
	    .'(SELECT COUNT(*) FROM `document` WHERE `type`=\'ttk\' AND `document`.`account_id`=`%1$s`.`account_id` AND `print` IS NOT NULL AND `document_date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\') AND CONCAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)),\' 23:59:59\')) AS `documents_printed`,'
*/
	    .'(SELECT `contract_number` FROM `contract` WHERE `contract`.`account_id`=`%1$s`.`account_id` ORDER BY `id` ASC LIMIT 1) AS `contract_number`,'
	    .'(SELECT `contact_name` FROM `contact` WHERE `contact`.`account_id`=`%1$s`.`account_id` ORDER BY `id` ASC LIMIT 1) AS `contact_name`,'
	    .'(SELECT `post` FROM `contact` WHERE `contact`.`account_id`=`%1$s`.`account_id` ORDER BY `id` ASC LIMIT 1) AS `contact_post`,'
	    .'(SELECT `phone_number` FROM `contact` WHERE `contact`.`account_id`=`%1$s`.`account_id` ORDER BY `id` ASC LIMIT 1) AS `contact_phone`,'
	    .'(SELECT `manager_name` FROM `manager` WHERE `manager`.`id`=`%1$s`.`manager_id`) AS `manager_name`,'
	    .'(SELECT `group_name` FROM `group` WHERE `group`.`id`=`%1$s`.`group_id`) AS `group_name`,'
	    .'(SELECT `status_name` FROM `status` WHERE `status`.`id`=`%1$s`.`status_id`) AS `status_name`,'
	    .'`customer`.`id` AS `id`'
	    .'%5$s'
	    .' FROM `%1$s`'
	    .' %2$s %3$s %4$s',
	    $this->_table,
	    $this->_where($this->_filter,'AND',false),
	    $this->_order(),
	    $this->_limit(),
	    is_array($this->_select)?sprintf(',%s',implode(',',$this->_select)):false
	);

/*
	echo '<pre>';
	print_r($this->_filter);
	echo '</pre>';
*/

	if ( $this->_result = $this->_doQuery($this->_query) )
	    return $this->_getDataArray();
    }
/*
    function countAll()
    {
	$this->_query = sprintf
	(
	    'SELECT COUNT(*) AS `total`'
	    .' FROM `%1$s`'
	    .' %2$s %3$s %4$s',
	    $this->_table,
	    $this->_where($this->_filter,'AND',false),
	    false,false
	);

	if ( $this->_result = $this->_doQuery($this->_query) )
	{
	    $row = $this->_getDataArray();
	    return $row[0]['total'];
	}
    }
*/
    function addFilter($filter)
    {
	if ( is_array($filter) )
	{
	    foreach ( $filter as $key => $value )
	    {
		if ( $key == 'q' )
		{
		    $this->_filter[] = $this->_where
		    (
			array
			(
			    array('`customer_name`','LIKE',mysql_real_escape_string(sprintf('%%%s%%',$value))),
			    array('`email`','LIKE',sprintf('%%%s%%',$value)),
			),
			'OR',true
		    );
		    continue;
		}

		if ( $key == 'contract_num' )
		{
		    $this->_filter[] = sprintf
		    (
			'('
			    .'SELECT COUNT(*) FROM `contract`'
				.' WHERE `contract`.`account_id`=`customer`.`account_id` AND `contract_number` LIKE \'%%%1$s%%\''
			.')>0',
			$value
		    );
		    continue;
		}

		if ( $key == 'show_deleted' ) continue;
		if ( $key == 'only_deleted' ) continue;

		if ( $key == 'create_date_begin' ) continue;
		if ( $key == 'create_date_end' ) continue;

		if ( $key == 'field' )
		{
		    $field = $value;
		    continue;
		}

		if ( $key == 'field_value' )
		{
		    if ( is_numeric($field) )
		    {
			$this->_filter[] = sprintf
			(
			    '('
				.'SELECT COUNT(*) FROM `arbitrary_field` af'
				    .' WHERE `af`.`customer_id`=`customer`.`id`'
				    .' AND `af`.`field_name_id`=%1$d'
				    .' AND `af`.`value` LIKE \'%%%2$s%%\''
			    .')>0',
			    $field,
			    $value
			);
			continue;
		    }
		    else
		    if ( is_string($field) )
		    {
			$this->_filter[] = sprintf('(`%s` LIKE \'%%%s%%\')',$field,$value);
			continue;
		    }
		}

		if ( $key == 'executive_id' )
		{
		    if ( is_numeric($value) )
		    {
			$executive = $value;
			$this->_filter[] = sprintf
			(
			    '('
				.'SELECT COUNT(*) FROM `contract` c'
				    .' WHERE `c`.`account_id`=`customer`.`account_id`'
					.' AND `c`.`executive_id`=%d'
				    .' LIMIT 1'
			    .')>0',
			    $value
			);
			continue;
		    }
		}

		if ( is_numeric($executive) && ($key=='documents') )
		{
		    switch($value)
		    {
			case	'complete':
			    $this->_filter[] = sprintf
			    (
				'('
				    .'SELECT COUNT(*) FROM `invoice` i'
					.' JOIN `contract` cn ON `cn`.`id`=`i`.`contract_id`'
					.' JOIN `customer` cs ON `cs`.`account_id`=`cn`.`account_id`'
					.' WHERE `date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\')'
						.'AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'
					    .' AND `cn`.`account_id`=`customer`.`account_id`'
					    .' AND `cn`.`executive_id`=%1$d'
					    .' AND `account` IS NOT NULL'
					    .' AND `act` IS NOT NULL %2$s'
/*
Убираем необходимость проверки наличия СФ (12.05.2012)
[9:17:03] Сергей Лагута: надо убрать необходимость сф
[9:17:07] Сергей Лагута: она для ип не нужна
-----------
					    .' AND `invoice` IS NOT NULL %2$s'
*/
				.')>0',
				$executive,
				($executive==0)?'AND `detail` IS NOT NULL':NULL
			    );
			    break;

			case 'no-complete':
			    $this->_filter[] = sprintf
			    (
				'('
				    .'SELECT COUNT(*) FROM `invoice` i'
					.' JOIN `contract` cn ON `cn`.`id`=`i`.`contract_id`'
					.' JOIN `customer` cs ON `cs`.`account_id`=`cn`.`account_id`'
					.' WHERE `date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\')'
						.'AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'
					    .' AND `cn`.`account_id`=`customer`.`account_id`'
					    .' AND `cn`.`executive_id`=%1$d'
					    .' AND `account` IS NOT NULL'
					    .' AND `act` IS NOT NULL'
					    .' AND `invoice` IS NOT NULL %2$s'
				.')=0',
				$executive,
				($executive==0)?'AND `detail` IS NOT NULL':NULL
			    );
			    break;

			case 'printed':
			    $this->_filter[] = sprintf
			    (
				'('
				    .'SELECT COUNT(*) FROM `invoice` i'
					.' JOIN `contract` cn ON `cn`.`id`=`i`.`contract_id`'
					.' JOIN `customer` cs ON `cs`.`account_id`=`cn`.`account_id`'
					.' WHERE `date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\')'
						.'AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'
					    .' AND `printed`=1'
					    .' AND `cn`.`executive_id`=%1$d'
					    .' AND `account` IS NOT NULL'
					    .' AND `act` IS NOT NULL'
					    .' AND `invoice` IS NOT NULL %2$s'
				.')>0',
				$executive,
				($executive==0)?'AND `detail` IS NOT NULL':NULL
			    );
			    break;

			case 'no-printed':
			    $this->_filter[] = sprintf
			    (
				'('
				    .'SELECT COUNT(*) FROM `invoice` i'
					.' JOIN `contract` cn ON `cn`.`id`=`i`.`contract_id`'
					.' JOIN `customer` cs ON `cs`.`account_id`=`cn`.`account_id`'
					.' WHERE `date` BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\')'
						.'AND LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'
					    .' AND `printed`=0'
					    .' AND `cn`.`account_id`=`customer`.`account_id`'
					    .' AND `cn`.`executive_id`=%1$d'
					    .' AND `account` IS NOT NULL'
					    .' AND `act` IS NOT NULL'
					    .' AND `invoice` IS NOT NULL %2$s'
				.')>0',
				$executive,
				($executive==0)?'AND `detail` IS NOT NULL':NULL
			    );
			    break;
		    }
		    continue;
		}

		$this->_filter[] = array($key,$value);
	    }
	}
	elseif ( is_string($filter) )
	    $this->_filter[] = $filter;
    }
}

class webCustomer extends WWW
{
    var $_customer;

    function __construct()
    {
	parent::__construct();
	$this->loadDictionary('status');
	$this->loadDictionary('group');
	$this->loadDictionary('default');
	$this->loadDictionary('custom');

	$this->_customer = new Customer;
	$this->_task = new Task;
    }

    function getList()
    {
	$this->_task->_filter[] = array('`close`',0);
	$this->_task->setLimit(10);
	$this->_task->setOrder('task_time','desc');

	$this->_smarty->assign('tasks',$this->_task->getList());
	$this->_smarty->assign('customers',$this->_customer->getList());
	$this->_smarty->assign('total_rows',$this->_customer->countAll());
	$this->_smarty->assign('limit_rows',$this->_customer->curLimit());
	$this->_smarty->assign('current_page',$this->_customer->curPage());
	$this->_smarty->assign('pages',ceil($this->_customer->countAll()/$this->_customer->curLimit()));
	$this->_smarty->assign('page',$this->_customer->curPage());
	$this->_smarty->assign('mysql_query_customers',$this->_customer->_query);

	$this->template('admin/cust-list.html');
	return $this;
    }

    function page($n)
    {
	$this->_customer->setPage($n);
	return $this;
    }

    function fields($fields)
    {
	foreach ( $fields as $key => $value )
	{
	    if ( $id = preg_replace('/^custom_field_(\d+)$/','$1',$key) )
	    {
		if ( !is_numeric($id) ) { continue; }

		$this->_customer->_select[] = sprintf
		(
		    '(SELECT `value` FROM `arbitrary_field`'
		    .' WHERE `field_name_id`=%2$d'
			.' AND `arbitrary_field`.`customer_id`=`customer`.`id`'
		    .' ORDER BY `id` ASC LIMIT 1) AS `%1$s`',
		    $key,$id
		);
	    }
	}
    }

    function limit($l)
    {
	$this->_customer->setLimit($l);
	return $this;
    }

    function total()
    {
	return $this->_customer->countAll();
    }

    function addFilter($filter)
    {
	$this->_customer->addFilter($filter);
    }
}
?>
